package org.ftz;
import junit.framework.TestCase;
import org.ftz.JudgeOnline.Java.ExecuteJava;
import org.ftz.control.DBUtil;
import org.ftz.pojo.Problem;
import org.ftz.pojo.Testcase;
import org.junit.Test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class test {
    @Test
    public void test1(){
        String doc="import java.util.Scanner;\n" +
                "\n" +
                "public class Main    {\n" +
                "    public static void main(String[] args) {\n" +
                "        Scanner sc=new Scanner(System.in);\n" +
                "        int i = sc.nextInt();\n" +
                "        System.out.println(i);\n" +
                "    }\n" +
                "}";
        ExecuteJava.runJava(doc,"3");
    }
    @Test
    public void test2(){ //插入
        Connection connection = null;
        PreparedStatement statement =null;
        Problem problem = new Problem(18,"1","12","12","12");
        try {
            connection = DBUtil.getConnection();  // 1. 建立连接
            String sql = "insert into oj_problem values(?,?,?,?,?)"; // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setInt(1,problem.getId());
            statement.setString(2,problem.getTitle());
            statement.setString(3,problem.getLevel());
            statement.setString(4,problem.getDescription());
            statement.setString(5,problem.getTestcasepath());
            int ret = statement.executeUpdate(); // 3. 执行 SQL 语句
            if(ret == 1){
                System.out.println("插入成功!");
            }else {
                System.out.println("插入失败!");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
    }
    public void deletebyId(int id){ //删除
        Connection connection = null;
        PreparedStatement statement = null;
        PreparedStatement statement1=null;
        try {
            connection = DBUtil.getConnection(); // 1. 建立连接
            String sql = "delete from oj_problem where id = ?"; // 2. 拼装 SQL 语句
            String sql2="delete from oj_test where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            statement1=connection.prepareStatement(sql2);
            statement1.setInt(1,id);
            int ret = statement.executeUpdate();  // 3. 执行 SQL 语句
            int res=statement1.executeUpdate();
            if(ret == 1&&res==1){
                System.out.println("删除成功!");
            }else {
                System.out.println("删除失败!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);  // 4. 关闭释放资源
        }
    }
    @Test
    public void test3(){
        deletebyId(1000);
    }
    @Test
    public void test4(){ //查询所有题目
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Problem> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();  // 1. 建立连接
            String sql = "select id,title,level from oj_problem"; // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery(); // 3. 执行 SQL 语句
            while (resultSet.next()){ // 4. 遍历结果集
                Problem problem = new Problem();
                problem.setId(resultSet.getInt("id"));
                problem.setTitle(resultSet.getString("title"));
                problem.setLevel(resultSet.getString("level"));
                list.add(problem);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        for(Problem problem:list){
            System.out.println(problem);
        }
    }
    public List<Problem> selectLimit(int a,int b){ //分页查询题目
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Problem> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection(); // 1. 建立连接
            String sql = "select id,title,level from oj_problem limit ?,?"; // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setInt(1,a);
            statement.setInt(2,b);
            resultSet = statement.executeQuery();// 3. 执行 SQL 语句
            while (resultSet.next()){  // 4. 遍历结果集
                Problem problem = new Problem();
                problem.setId(resultSet.getInt("id"));
                problem.setTitle(resultSet.getString("title"));
                problem.setLevel(resultSet.getString("level"));
                list.add(problem);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);// 5. 关闭释放资源
        }
        for(Problem problem:list){
            System.out.println(problem);
        }
        return list;
    }
    public Problem selectBId(int id){ //根据题目编号查询题目
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Problem problem = new Problem();
        try {
            connection = DBUtil.getConnection(); // 1. 建立连接
            String sql = "select * from oj_problem where id=?";  // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            resultSet = statement.executeQuery();  // 3. 执行 SQL 语句
            while (resultSet.next()){ // 4. 遍历结果集
                problem.setId(resultSet.getInt("id"));
                problem.setTitle(resultSet.getString("title"));
                problem.setLevel(resultSet.getString("level"));
                problem.setDescription(resultSet.getString("description"));
                problem.setTestcasepath(resultSet.getString("testcasepath"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        return problem;
    }
    public Testcase selectTestById(int id){ //根据题目编号查询测试数据
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Testcase testcase = new Testcase();
        try {
            connection = DBUtil.getConnection(); // 1. 建立连接
            String sql = "select * from oj_test where id=?"; // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            resultSet = statement.executeQuery(); // 3. 执行 SQL 语句
            while (resultSet.next()){ // 4. 遍历结果集
                testcase.setId(resultSet.getInt("id"));
                testcase.setInput(resultSet.getString("input"));
                testcase.setOutput(resultSet.getString("output"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        return testcase;
    }
    @Test
    public void test09(){
        Testcase testcase = selectTestById(1000);
        System.out.println(testcase.getInput());
        System.out.println("---------");
        System.out.println(testcase.getOutput());
    }
    @Test
    public void test08() throws SQLException {
        Connection connection=null;
        PreparedStatement statement=null;
        PreparedStatement statement2=null;
        Problem problem = new Problem();
        problem.setId(1000);
        problem.setTitle("波动数列");
        problem.setDescription("dfdgfhcxvfggrg");
        problem.setLevel("low");
        try {
            connection=DBUtil.getConnection();
            String sql1="insert into oj_problem values(?,?,?,?,?)";
            String sql2="insert into oj_test values(?,?,?)";
            statement=connection.prepareStatement(sql1);
            statement2=connection.prepareStatement(sql2);
            statement.setInt(1,problem.getId());
            statement.setString(2,problem.getTitle());
            statement.setString(3,problem.getLevel());
            statement.setString(4,problem.getDescription());
            statement.setString(5,"");
            statement2.setInt(1,problem.getId());
            statement2.setString(2,"ert");
            statement2.setString(3,"eregth");
            int res=statement.executeUpdate();
            int res2=statement2.executeUpdate();
            if(res==1&&res2==1){
                System.out.println("插入成功！");
            }else {
                System.out.println("插入失败！");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
            statement2.close();
        }
    }
    @Test
    public void test7(){
        selectLimit(2,3);
    }
    @Test
    public void test8(){
        Problem problem = selectBId(30);
        System.out.println(problem);
    }
    @Test
    public void test6(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int rowCount=0;
        try {
            connection = DBUtil.getConnection();  // 1. 建立连接
            String sql = "select count(*) from oj_problem";  // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery(); // 3. 执行 SQL 语句
            if(resultSet.next()){  // 4. 遍历结果集
                rowCount=resultSet.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        System.out.println(rowCount);
    }
    @Test
    public void test5(){ //查找对应id题目
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();  // 1. 建立连接
            String sql = "select * from oj_problem where id = ?"; // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setInt(1,3);
            resultSet =  statement.executeQuery(); // 3. 执行 SQL 语句
            if (resultSet.next()){ // 4. 遍历结果集
                Problem problem = new Problem();
                problem.setId(resultSet.getInt("id"));
                problem.setTitle(resultSet.getString("title"));
                problem.setLevel(resultSet.getString("level"));
                problem.setDescription(resultSet.getString("description"));
                System.out.println(problem);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
    }
}
